import frappe
import json
from frappe.utils import now_datetime
from datetime import datetime
# 缓存键前缀
CACHE_PREFIX = "_link_history"

# 自定义 JSON 编码器，处理 datetime 对象
class DateTimeEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        return super().default(obj)

def get_link_history(link_doctype, reference_doctype=None, limit=10):
    """
    获取用户的链接字段历史记录
    
    参数:
        link_doctype: 链接字段指向的文档类型
        reference_doctype: 可选，使用链接字段的文档类型（现在忽略此参数）
        limit: 返回的历史记录数量
    """
    # 检查是否禁用
    if is_link_history_disabled():
        return []
    
    user = frappe.session.user
    
    # 获取缓存键
    cache_key = f"{link_doctype}:all:{user}"
    
    try:
        # 从缓存中获取历史记录
        cached_history = frappe.cache.hget(CACHE_PREFIX, cache_key)
        
        # 检查缓存是否存在且未过期
        cache_timestamp = frappe.cache.hget(CACHE_PREFIX, f"{cache_key}:timestamp")
        
        # 检查缓存是否过期
        cache_expired = True
        if cache_timestamp:
            try:
                # 如果 cache_timestamp 是 bytes 类型，转换为字符串
                if isinstance(cache_timestamp, bytes):
                    cache_timestamp = cache_timestamp.decode('utf-8')
                
                # 如果 cache_timestamp 是字符串，转换为 datetime 对象
                if isinstance(cache_timestamp, str):
                    cache_timestamp = datetime.strptime(cache_timestamp, '%Y-%m-%d %H:%M:%S')
                
                # 计算时间差
                time_diff = (now_datetime() - cache_timestamp).total_seconds()
                cache_expired = time_diff > 3600  # 改为1小时过期，与定时任务同步
            except Exception as e:
                frappe.log_error(f"检查缓存过期时出错: {str(e)[:50]}", "Link History")
                cache_expired = True
        
        # 解析缓存的历史记录
        cached_data = []
        if cached_history:
            try:
                # 如果 cached_history 是 bytes 类型，转换为字符串
                if isinstance(cached_history, bytes):
                    cached_history = cached_history.decode('utf-8')
                
                cached_data = json.loads(cached_history)
            except Exception as e:
                frappe.log_error(f"解析历史记录时出错: {str(e)[:100]}", "Link History")
        
        # 检查是否需要从数据库刷新
        if not cached_data or cache_expired:
            # 如果缓存不存在或已过期，从数据库中获取
            try:
                # 使用直接 SQL 查询
                result = frappe.db.sql("""
                    SELECT link_name, link_title, frequency, last_used
                    FROM `tabLink History Record`
                    WHERE user=%s AND link_doctype=%s
                    ORDER BY frequency DESC, last_used DESC
                    LIMIT %s
                """, (user, link_doctype, int(limit)), as_dict=True)
                
                # 处理 datetime 对象
                db_history = []
                for record in result:
                    if record is None:
                        continue
                    
                    record_dict = record
                    
                    # 确保 last_used 是字符串
                    if 'last_used' in record_dict and record_dict['last_used']:
                        if isinstance(record_dict['last_used'], datetime):
                            record_dict['last_used'] = record_dict['last_used'].strftime('%Y-%m-%d %H:%M:%S')
                    
                    db_history.append(record_dict)
                
                # 检查是否有"脏"标记
                dirty_key = f"{link_doctype}:all:{user}"
                is_dirty = frappe.cache.hget(f"{CACHE_PREFIX}:dirty", dirty_key)
                
                if is_dirty and cached_data:
                    # 如果有"脏"标记且缓存中有数据，合并数据库和缓存数据
                    merged_history = merge_history(db_history, cached_data)
                    
                    # 更新缓存但保持"脏"标记
                    frappe.cache.hset(CACHE_PREFIX, cache_key, json.dumps(merged_history, cls=DateTimeEncoder))
                    frappe.cache.hset(CACHE_PREFIX, f"{cache_key}:timestamp", now_datetime().strftime('%Y-%m-%d %H:%M:%S'))
                    
                    return merged_history[:int(limit)]
                else:
                    # 如果没有"脏"标记或缓存为空，直接使用数据库数据
                    frappe.cache.hset(CACHE_PREFIX, cache_key, json.dumps(db_history, cls=DateTimeEncoder))
                    frappe.cache.hset(CACHE_PREFIX, f"{cache_key}:timestamp", now_datetime().strftime('%Y-%m-%d %H:%M:%S'))
                    
                    return db_history
            except Exception as e:
                frappe.log_error(f"获取历史记录时出错: {str(e)[:100]}", "Link History")
                # 如果数据库查询失败，返回缓存数据（即使已过期）
                return cached_data
        
        # 如果缓存未过期或上面的逻辑没有返回，返回缓存数据
        return cached_data
    except Exception as e:
        frappe.log_error(f"获取链接历史记录时出错: {str(e)[:100]}", "Link History")
        return []

def merge_history(db_history, cached_history):
    """合并数据库和缓存中的历史记录"""
    # 创建一个字典，键为 link_name
    merged = {}
    
    # 首先添加数据库中的记录
    for item in db_history:
        link_name = item.get("link_name")
        if link_name:
            merged[link_name] = item
    
    # 然后添加或更新缓存中的记录
    for item in cached_history:
        link_name = item.get("link_name")
        if not link_name:
            continue
            
        if link_name in merged:
            # 如果记录已存在，使用频率更高的版本
            db_freq = merged[link_name].get("frequency", 0)
            cache_freq = item.get("frequency", 0)
            
            if cache_freq > db_freq:
                merged[link_name] = item
        else:
            # 如果记录不存在，添加它
            merged[link_name] = item
    
    # 将字典转换回列表并按频率和最后使用时间排序
    result = list(merged.values())
    result.sort(key=lambda x: (x.get("frequency", 0), x.get("last_used", "")), reverse=True)
    
    return result

def update_link_history(link_doctype, link_name, reference_doctype=None):
    """
    更新链接字段的历史记录
    
    参数:
        link_doctype: 链接字段指向的文档类型
        link_name: 链接字段的值
        reference_doctype: 可选，使用链接字段的文档类型（现在只用于记录，不用于分组）
    """
    # 检查是否禁用
    if is_link_history_disabled():
        return
    
    if not link_name or not link_doctype:
        return
        
    # 检查文档类型是否显示标题
    show_title = frappe.get_cached_value('DocType', link_doctype, 'show_title_field_in_link') or False
    
    # 获取文档的标题
    link_title = link_name
    if show_title:
        try:
            doc = frappe.get_doc(link_doctype, link_name)
            # 使用 get_title 方法获取标题，这会遵循 Frappe 的标题字段设置
            link_title = doc.get_title() if hasattr(doc, "get_title") else link_name
        except:
            pass
    
    # 获取缓存键 - 忽略 reference_doctype
    user = frappe.session.user
    cache_key = f"{link_doctype}:all:{user}"
    
    # 从缓存中获取历史记录
    history = frappe.cache.hget(CACHE_PREFIX, cache_key) or []
    if history and isinstance(history, (str, bytes)):
        try:
            # 如果 history 是 bytes 类型，转换为字符串
            if isinstance(history, bytes):
                history = history.decode('utf-8')
            
            history = json.loads(history)
        except:
            history = []
    
    # 查找现有记录
    existing_item = None
    for item in history:
        if item.get("link_name") == link_name:
            existing_item = item
            break
    
    # 获取当前时间并转换为字符串
    current_time = now_datetime()
    current_time_str = current_time.strftime('%Y-%m-%d %H:%M:%S')
    
    if existing_item:
        # 更新现有记录
        existing_item["frequency"] = existing_item.get("frequency", 0) + 1
        existing_item["last_used"] = current_time_str  # 使用字符串而不是 datetime 对象
        existing_item["link_title"] = link_title
        
        # 更新 reference_doctype 列表
        ref_doctypes = existing_item.get("reference_doctypes", [])
        if reference_doctype and reference_doctype not in ref_doctypes:
            ref_doctypes.append(reference_doctype)
            existing_item["reference_doctypes"] = ref_doctypes
    else:
        # 创建新记录
        new_item = {
            "link_doctype": link_doctype,
            "link_name": link_name,
            "link_title": link_title,
            "frequency": 1,
            "last_used": current_time_str  # 使用字符串而不是 datetime 对象
        }
        
        # 添加 reference_doctype 列表
        if reference_doctype:
            new_item["reference_doctypes"] = [reference_doctype]
            
        history.append(new_item)
    
    # 按使用频率和最后使用时间排序
    history.sort(key=lambda x: (x.get("frequency", 0), x.get("last_used", "")), reverse=True)
    
    # 更新缓存 - 使用 json.dumps 而不是 DateTimeEncoder
    frappe.cache.hset(CACHE_PREFIX, cache_key, json.dumps(history))
    
    # 标记需要更新数据库 - 修改脏标记的键格式
    dirty_key = f"{link_doctype}:all:{user}"
    frappe.cache.hset(f"{CACHE_PREFIX}:dirty", dirty_key, 1)
    
    # 如果启用了后台作业，安排一个作业来更新数据库
    if frappe.flags.in_web_form or frappe.flags.in_app:
        frappe.enqueue(
            "link_history.utils.link_history.sync_history_to_db",  # 修正路径
            link_doctype=link_doctype,
            reference_doctype=reference_doctype,
            user=user,
            now=frappe.flags.in_test
        )
    
    return {"status": "success"}

def sync_history_to_db(link_doctype, reference_doctype=None, user=None):
    """将缓存中的历史记录同步到数据库"""
    if not user:
        user = frappe.session.user
    
    # 获取缓存键
    cache_key = f"{link_doctype}:all:{user}"
    dirty_key = f"{link_doctype}:all:{user}"
    
    # 检查是否需要更新数据库
    dirty_value = frappe.cache.hget(f"{CACHE_PREFIX}:dirty", dirty_key)
    if not dirty_value:
        return
    
    # 从缓存中获取历史记录
    history = frappe.cache.hget(CACHE_PREFIX, cache_key) or []
    if history and isinstance(history, (str, bytes)):
        try:
            if isinstance(history, bytes):
                history = history.decode('utf-8')
            history = json.loads(history)
        except Exception as e:
            frappe.log_error(f"解析历史记录出错: {str(e)[:50]}", "Link History")
            history = []
    
    
    # 如果没有历史记录，直接返回
    if not history:
        try:
            frappe.cache.hdel(f"{CACHE_PREFIX}:dirty", dirty_key)
        except Exception as e:
            frappe.log_error(f"清除脏标记出错: {str(e)[:50]}", "Link History")
        return
    
    # 更新数据库 - 不使用事务
    success_count = 0
    error_count = 0
    
    for item in history:
        try:
            link_name = item.get("link_name")
            if not link_name:
                continue
            
            # 在事务外检查记录是否存在
            # 使用 SQL 查询而不是 frappe.get_list() 来避免可能的隐式提交
            existing_record = None
            try:
                result = frappe.db.sql("""
                    SELECT name 
                    FROM `tabLink History Record` 
                    WHERE user=%s AND link_doctype=%s AND link_name=%s 
                    LIMIT 1
                """, (user, link_doctype, link_name), as_dict=True)
                
                if result and len(result) > 0:
                    existing_record = result[0].name
            except Exception as e:
                frappe.log_error(f"查询记录存在出错: {str(e)[:50]}", "Link History")
                continue  # 如果查询出错，跳过此记录
            
            # 处理 last_used
            last_used = item.get("last_used")
            if last_used:
                if isinstance(last_used, str):
                    try:
                        # 移除重复导入
                        # from datetime import datetime
                        last_used = datetime.strptime(last_used, '%Y-%m-%d %H:%M:%S')
                    except ValueError:
                        last_used = now_datetime()
            else:
                last_used = now_datetime()
            
            # 不使用事务，直接执行 SQL
            try:
                if existing_record:
                    # 更新现有记录 - 使用 SQL 更新而不是 frappe.get_doc()
                    frappe.db.sql("""
                        UPDATE `tabLink History Record` 
                        SET frequency=%s, last_used=%s, link_title=%s, reference_doctype=%s 
                        WHERE name=%s
                    """, (
                        item.get("frequency", 1),
                        last_used,
                        item.get("link_title", link_name),
                        reference_doctype or "",
                        existing_record
                    ))
                    
                    success_count += 1
                else:
                    # 创建新记录 - 使用 SQL 插入而不是 frappe.new_doc()
                    frappe.db.sql("""
                        INSERT INTO `tabLink History Record` 
                        (name, user, link_doctype, link_name, link_title, frequency, last_used, reference_doctype, creation, modified, modified_by, owner) 
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """, (
                        frappe.generate_hash(length=10),  # 生成一个随机的名称
                        user,
                        link_doctype,
                        link_name,
                        item.get("link_title", link_name),
                        item.get("frequency", 1),
                        last_used,
                        reference_doctype or "",
                        now_datetime(),
                        now_datetime(),
                        user,
                        user
                    ))
                    
                    success_count += 1
            except Exception as e:
                error_count += 1
                frappe.log_error(f"处理记录SQL出错: {str(e)[:50]}", "Link History")
        except Exception as e:
            error_count += 1
            frappe.log_error(f"处理记录项出错: {str(e)[:50]}", "Link History")
    
    # 清除脏标记
    try:
        frappe.cache.hdel(f"{CACHE_PREFIX}:dirty", dirty_key)
    except Exception as e:
        frappe.log_error(f"清除脏标记出错: {str(e)[:50]}", "Link History")

def batch_update_history():
    """批量更新历史记录到数据库"""
    try:
        # 获取所有需要更新的缓存键
        dirty_keys = frappe.cache.hgetall(f"{CACHE_PREFIX}:dirty") or {}
        
        for dirty_key, value in dirty_keys.items():
            try:
                # 如果 dirty_key 是 bytes 类型，转换为字符串
                if isinstance(dirty_key, bytes):
                    dirty_key = dirty_key.decode('utf-8')
                
                parts = dirty_key.split(":")
                if len(parts) >= 2:
                    link_doctype = parts[0]
                    user = parts[2] if len(parts) >= 3 else frappe.session.user
                    reference_doctype = None  # 我们不再使用 reference_doctype 进行分组
                    
                    # 同步到数据库
                    sync_history_to_db(link_doctype, reference_doctype, user)
                else:
                    frappe.log_error(f"无效的缓存键格式: {dirty_key[:30]}", "Link History")
            except Exception as e:
                frappe.log_error(f"处理缓存键出错: {str(e)[:100]}", "Link History")
    except Exception as e:
        frappe.log_error(f"批量更新出错: {str(e)[:30]}", "Link History")

def clear_link_history(link_doctype=None, reference_doctype=None):
    """清除用户的链接历史记录"""
    user = frappe.session.user
    
    filters = {"user": user}
    
    if link_doctype:
        filters["link_doctype"] = link_doctype
    
    # reference_doctype 现在只是一个可选的过滤条件
    if reference_doctype:
        filters["reference_doctype"] = reference_doctype
    
    frappe.db.delete("Link History Record", filters)
    
    # 清除缓存
    if link_doctype:
        # 使用统一的缓存键
        cache_key = f"{link_doctype}:all:{user}"
        frappe.cache.hset(CACHE_PREFIX, cache_key, None)
        frappe.cache.hdel(CACHE_PREFIX, cache_key)
    else:
        # 清除所有缓存
        frappe.cache.delete_key(CACHE_PREFIX)

@frappe.whitelist()
def manual_sync_history():
    """手动触发历史记录同步"""
    try:
        batch_update_history()
        return {"status": "success", "message": "历史记录同步已触发"}
    except Exception as e:
        return {"status": "error", "message": f"历史记录同步失败: {e}"}

@frappe.whitelist()
def direct_sync_history(link_doctype, link_name, reference_doctype=None):
    """直接将历史记录同步到数据库，绕过缓存机制"""
    try:
        user = frappe.session.user
        
        # 检查文档类型是否显示标题
        show_title = frappe.get_cached_value('DocType', link_doctype, 'show_title_field_in_link') or False
        
        # 获取文档的标题
        link_title = link_name
        if show_title:
            try:
                doc = frappe.get_doc(link_doctype, link_name)
                # 使用 get_title 方法获取标题，这会遵循 Frappe 的标题字段设置
                link_title = doc.get_title() if hasattr(doc, "get_title") else link_name
            except:
                pass
        
        # 在事务外检查记录是否存在
        # 使用 SQL 查询而不是 frappe.get_list() 来避免可能的隐式提交
        existing_record = None
        try:
            result = frappe.db.sql("""
                SELECT name 
                FROM `tabLink History Record` 
                WHERE user=%s AND link_doctype=%s AND link_name=%s 
                LIMIT 1
            """, (user, link_doctype, link_name), as_dict=True)
            
            if result and len(result) > 0:
                existing_record = result[0].name
        except Exception as e:
            frappe.log_error(f"查询记录存在出错: {str(e)[:50]}", "Link History")
        
        # 不使用事务，直接执行 SQL
        try:
            if existing_record:
                # 更新现有记录 - 使用 SQL 更新而不是 frappe.get_doc()
                frappe.db.sql("""
                    UPDATE `tabLink History Record` 
                    SET frequency=frequency+1, last_used=%s, link_title=%s, reference_doctype=%s 
                    WHERE name=%s
                """, (
                    now_datetime(),
                    link_title,
                    reference_doctype or "",
                    existing_record
                ))
            
            return {"status": "success", "message": "历史记录已直接同步到数据库"}
        except Exception as e:
            frappe.log_error(f"执行SQL出错: {str(e)[:50]}", "Link History")
            return {"status": "error", "message": f"同步失败: {e}"}
    except Exception as e:
        frappe.log_error(f"直接同步历史记录时出错: {str(e)[:100]}", "Link History")
        return {"status": "error", "message": f"同步失败: {e}"}

@frappe.whitelist()
def get_history(link_doctype, reference_doctype=None, limit=10):
    """API 端点，获取链接历史记录"""
    try:
        limit = int(limit) if limit else 10
        history = get_link_history(link_doctype, reference_doctype, limit)
        return {"status": "success", "data": history}
    except Exception as e:
        return {"status": "error", "message": str(e)[:100], "data": []}

def is_link_history_disabled():
    """检查 Link History 功能是否被禁用"""
    try:
        return frappe.db.get_single_value("Link History Settings", "disable_link_history") or False
    except Exception:
        return False

def get_link_history_limit():
    """获取链接历史记录显示条数限制"""
    try:
        limit = frappe.db.get_single_value("Link History Settings", "link_history_results_limit")
        return int(limit) if limit else 5  # 默认显示5条
    except Exception:
        return 5  # 出错时返回默认值 